/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/

/************************************************************/
/*****              Archives Comment                    *****/
/*****                                                  *****/
/************************************************************/

/** Create Table **/

if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Triax_ProductComment]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		CREATE TABLE {databaseOwner}[{objectQualifier}Triax_ProductComment]
		(
			[ModuleID] [int] NOT NULL,
			[CommentID] [int] NOT NULL IDENTITY(1, 1),
			[Title] [ntext] NOT NULL,
			[Content] [ntext],
			[Category] [int],
			[Status] [int],
			[Date] [datetime],
			[ProductID] [int] NULL,
			[CreatedByUser] [int] NOT NULL,
			[CreatedDate] [datetime] NOT NULL,
			[UpdatedByUser] [int] NOT NULL,
			[UpdatedDate] [datetime] NOT NULL	
		)

		ALTER TABLE {databaseOwner}[{objectQualifier}Triax_ProductComment] ADD CONSTRAINT [PK_{objectQualifier}Triax_ProductComment] PRIMARY KEY CLUSTERED  ([CommentID])
		CREATE NONCLUSTERED INDEX [IX_{objectQualifier}Triax_ProductComment] ON {databaseOwner}[{objectQualifier}Triax_ProductComment] ([ModuleID])

		ALTER TABLE {databaseOwner}[{objectQualifier}Triax_ProductComment] WITH NOCHECK ADD CONSTRAINT [FK_{objectQualifier}Triax_ProductComment_{objectQualifier}Modules] FOREIGN KEY ([ModuleID]) REFERENCES {databaseOwner}[{objectQualifier}Modules] ([ModuleID]) ON DELETE CASCADE NOT FOR REPLICATION
		ALTER TABLE {databaseOwner}[{objectQualifier}Triax_ProductComment] WITH NOCHECK ADD CONSTRAINT [FK_{objectQualifier}Triax_ProductComment_{objectQualifier}Store_Products] FOREIGN KEY ([ProductID]) REFERENCES {databaseOwner}[{objectQualifier}Store_Products] ([ProductID])
	END
GO


/** Drop Existing Stored Procedures **/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Triax_GetProductComments]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}Triax_GetProductComments
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Triax_GetProductComment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}Triax_GetProductComment
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Triax_AddProductComment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}Triax_AddProductComment
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Triax_UpdateProductComment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}Triax_UpdateProductComment
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Triax_DeleteProductComment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}Triax_DeleteProductComment
GO

/** Create Stored Procedures **/

create procedure {databaseOwner}{objectQualifier}Triax_GetAllProductComments

	@ModuleId int,
	@ProductID int

as

select ModuleId,
       CommentId,
	Title,
        Content,
        Category,
        Status,
        Date,
        ProductID,
        CreatedByUser,
        CreatedDate,
        UpdatedByUser,
        UpdatedDate	
from {objectQualifier}Triax_ProductComment with (nolock)
where ProductID = @ProductID
GO


create procedure {databaseOwner}{objectQualifier}Triax_GetProductComments

	@ModuleId int,
	@ProductID int,
	@Status int

as

select ModuleId,
       CommentId,
	Title,
        Content,
        Category,
        Status,
        Date,
        ProductID,
        CreatedByUser,
        CreatedDate,
        UpdatedByUser,
        UpdatedDate	
from {objectQualifier}Triax_ProductComment with (nolock)
where  Status = @Status and ProductID = @ProductID
GO

create procedure {databaseOwner}{objectQualifier}Triax_GetProductComment

	@ModuleId int,
    @CommentId int

as

select ModuleId,
       CommentId,
		Title,
        Content,
        Category,
        Status,
        Date,
        ProductID,
        CreatedByUser,
        CreatedDate,
        UpdatedByUser,
        UpdatedDate	
from {objectQualifier}Triax_ProductComment with (nolock)
where CommentId = @CommentId
GO


create procedure {databaseOwner}{objectQualifier}Triax_AddProductComment

	@ModuleId       int,
    @Title	ntext,
    @Content	ntext	,
    @Category	int	,
    @Status	int	,
    @Date	    datetime	,
    @ProductID	int	,
	@UserID         int

as

insert into {objectQualifier}Triax_ProductComment (
	ModuleId,
    Title,
    Content,
    Category,
    Status,
    Date,
    ProductID,
    CreatedByUser,
    CreatedDate,
    UpdatedByUser,
    UpdatedDate	
) 
values (
	@ModuleId,
    @Title,
    @Content,
    @Category,
    @Status,
    @Date,
    @ProductID,
	@UserID,
	getdate(),
	@UserID,
	getdate()	
)

GO

create procedure {databaseOwner}{objectQualifier}Triax_UpdateProductComment

	@ModuleId       int,
        @CommentId         int,
    @Title	ntext,
    @Content	ntext	,
    @Category	int	,
    @Status	int	,
    @Date	    datetime	,
    @ProductID	int	,
	@UserID         int

as

update {objectQualifier}Triax_ProductComment
set
    Title	= @Title,
    Content	= @Content,
    Category	= @Category,
    Status	= @Status,
    Date	    = @Date,
    ProductID	= @ProductID,
    CreatedByUser =	@UserID,
    CreatedDate	  =	getdate(),
    UpdatedByUser =	@UserID,
    UpdatedDate	  =	getdate()

where  CommentId = @CommentId

GO

create procedure {databaseOwner}{objectQualifier}Triax_DeleteProductComment

	@ModuleId       int,
        @CommentId         int

as

delete
from   {objectQualifier}Triax_ProductComment
where  CommentId = @CommentId

GO